import MySQLdb
from .pool import Pool
from time import sleep

class DbConnection:

    isCommit = False
    isClose = False

    def __init__(self, pool=None, **kwargs):
        self.__dict__.update(kwargs)
        self.tb_prefix = kwargs.pop('tb_prefix', '')
        self.proc_prefix = kwargs.pop('proc_prefix', 'proc_')
        if pool is None:
            self.conn = MySQLdb.connect(**kwargs)
        else:
            self.conn = pool.connection()
        self.cursor = self.conn.cursor(MySQLdb.cursors.SSDictCursor)

    def __getattr__(self, name):
        if name in self.__dict__:
            return self.__dict__[name]

        if hasattr(self.cursor, name):
            return getattr(self.cursor, name)

        return getattr(self.conn, name)

    def commit(self):
        self.conn.commit()
        self.isCommit = False

    def use(self, dbname):
        self.execute('USE %s' % dbname)
        return self

    def close(self):
        #  print('Db close: ', self.isClose, self.isCommit)
        if self.isClose:
            return
        if self.isCommit:
            self.commit()
        self.cursor.close()
        self.conn.close()
        self.isClose = True

    def proc(self, name, args=None):
        """
        存储过程执行
        """
        args = () if args is None else args
        args = (args, ) if not isinstance(args, tuple) else args

        self.callproc(name, args)

        return self.fetchall()

    def comb(self, sep=',', fmt='%s=%%s', **kwargs):
        """
        将字典分离成key字符和元组值
        """
        keys = tuple(kwargs.keys())
        if not keys:
            return '', ()

        return (sep.join([fmt % k for k in keys]),
                ([kwargs.get(x) for x in keys]))

    def where(self, kwargs, sql, values=()):
        """
        简单的由字典转化成查询条件
        """
        if isinstance(kwargs, (tuple, list)) and kwargs:
            return "%s WHERE %s" % (sql, kwargs[0]), (list(kwargs[1]) + list(values))

        elif isinstance(kwargs, str) and kwargs:
            return '%s WHERE %s' % (sql, kwargs), values

        elif isinstance(kwargs, dict) and kwargs:
            con, vals = self.comb(' AND ', **kwargs)
            return '%s WHERE %s' % (sql, con), (list(vals) + list(values))

        return sql, values

    def exists(self, table, field='*', **kwargs):
        """
        检测数据是否存在, 存在返回数据
        """
        where = kwargs['where'] if kwargs.get('where') else kwargs
        return self.select(table, where, field=field)

    def count(self, table, name='total', **kwargs):
        """
        统计数据个数
        """
        field = 'COUNT(*) as ' + name
        where = kwargs['where'] if kwargs.get('where') else kwargs
        return self.select(table, where, field=field, colname=name)

    def sum(self, table, field, name='total', **kwargs):
        """
        统计查询之和
        """
        field = 'IFNULL(SUM(%s), 0) as %s' % (field, 'total')
        where = kwargs['where'] if kwargs.get('where') else kwargs
        return self.select(table, where, field=field, colname=name)

    def table(self, name):
        name = name.split(':')
        alias = name[1] if len(name) > 1 else name[0]
        name = name[0]

        if not name.startswith(self.tb_prefix):
            name = "%s%s" % (self.tb_prefix, name)

        return '`%s` AS `%s`' % (name, alias)

    def wait_exec(self, sql, values=(), num=1):
        """
        执行SQL语句, 失败重执行机制
        """
        data = None

        if sql.startswith('SELECT') or sql.startswith('select'):
            isSelect = True
        else:
            isSelect = False

        for c in range(num):
            ret = self.execute(sql, values)
            #  print(c, isSelect, sql, values, ret)
            if isSelect:
                data = self.fetchall()
            else:
                data = ret

            if data:
                break

            sleep(0.1)

        return data

    def select(self, name, where=None, **kwargs):
        """
        查询语句
        """
        table = self.table(name)

        sql = 'SELECT %s FROM %s' % (kwargs.get('field', '*'), table)

        if kwargs.get('join') not in (None, ''):
            sql = '%s LEFT JOIN %s%s' % (sql, self.tb_prefix, kwargs['join'])

        sql, values = self.where(where, sql, kwargs.get('values', ()))

        if kwargs.get('group') not in (None, ''):
            sql = '%s GROUP BY %s' % (sql, kwargs['group'])

            if kwargs.get('having') not in (None, ''):
                sql = '%s HAVING %s' % (sql, kwargs['having'])

        if kwargs.get('order') not in (None, ''):
            sql = '%s ORDER BY %s' % (sql, kwargs['order'])

        if kwargs.get('limit') not in (None, ''):
            sql = '%s LIMIT %s' % (sql, kwargs['limit'])

        #  print('select: ', sql, values)

        if kwargs.get('sql', False):
            return sql

        data = self.wait_exec(sql, values)

        if kwargs.get('colname') not in (None, ''):
            return data[0][kwargs['colname']]
        return data

    def insert(self, table, field, values=None, op='INSERT'):
        """
        插入语句
        """
        if isinstance(field, dict):
            field, values = self.comb(',', '%s', **field)
            values = [values]

        table = '%s%s' % (self.tb_prefix, table)
        vals = ('%s,' * len(field.split(','))).strip(',')
        sql = '%s `%s`(%s) VALUES(%s)' % (op, table, field, vals)
        self.isCommit = True
        #  print('%s: ' % op, sql, values)
        return self.executemany(sql, values)

    def replace(self, table, field, values=None):
        """
        替换语句
        """
        return self.insert(table, field, values, 'REPLACE')

    def update(self, table, where=None, **kwargs):
        """
        更新语句
        """
        sql = 'UPDATE `%s%s` SET ' % (self.tb_prefix, table);
        expr, expr_vals = self.comb(',', **kwargs)
        sql = '%s %s' % (sql, expr)
        self.isCommit = True
        sql, values = self.where(where, sql)
        #  print('update: ', sql, expr_vals, values)
        return self.wait_exec(sql, list(expr_vals) + list(values))

    def delete(self, table, **kwargs):
        """
        删除语句
        """
        sql = 'DELETE FROM `%s%s`' % (self.tb_prefix, table);
        self.isCommit = True
        sql, values = self.where(kwargs, sql)
        #  print('delete: ', sql, values)
        return self.execute(sql, values)
